package com.groot.medical.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.groot.common.util.MedicalDBUtil;
import com.groot.medical.entity.AdministrativeArea;



public class AdministrativeAreaDaoImpl implements AdministrativeAreaDao {
	Connection conn = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	@Override
	public int getAllAreaCount() {
		int i =0;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		try {
		conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
		String sql="SELECT count(1) AS id FROM t_area";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		if(rs.next()){
			i=rs.getInt("id");
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return i;
	}
	@Override
	public List<AdministrativeArea> findAreaByPage(int startIndex, int pageSize) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		String sql = "select * from t_area limit ?,?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, startIndex);
			ps.setInt(2, pageSize);
			rs = ps.executeQuery();
			List<AdministrativeArea> areaList = new ArrayList<>();
			
			while(rs.next()){
				/*sysUser = new SysUser(rs.getString(2), rs.getString(3), rs.getString(4));*/
				AdministrativeArea area = new AdministrativeArea();
				area.setId(rs.getInt(1));
				area.setPid(rs.getInt(2));
				area.setAreaCode(rs.getString(3));
				area.setAreaName(rs.getString(4));
				area.setAreaLevel(rs.getString(5));
				
				
				areaList.add(area);
				
			}
			
			return areaList;
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public List<AdministrativeArea> findAllAreaInfo() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="select * from t_area";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public int addAreaInfo(AdministrativeArea area) {
		int i =0;
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			String sql="insert into t_area(pid,area_code,area_name,area_level)values(?,?,?,?)";
			int Level=Integer.parseInt(area.getAreaLevel())+1;
			ps=conn.prepareStatement(sql);
			ps.setString(1,String.valueOf(area.getPid()) );
			ps.setString(2,area.getAreaCode());
			ps.setString(3,area.getAreaName());
			ps.setString(4,String.valueOf(Level));
			
			i= ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
			return i;
	}
	@Override
	public AdministrativeArea findLevelByPid(int pid) {
		String level = null;
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		/*PreparedStatement ps = null;
		ResultSet rs = null;*/
		
		String sql = "select * from t_area where id=?";
		
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, pid);
			rs = ps.executeQuery();
			/*SysUser sysUser = null;*/
			if(rs.next()){
				AdministrativeArea area = new AdministrativeArea();
				area.setId(rs.getInt(1));
				area.setPid(rs.getInt(2));
				area.setAreaCode(rs.getString(3));
				area.setAreaName(rs.getString(4));
				area.setAreaLevel(rs.getString(5));
				return area;
			}
			
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return null;
	}
	@Override
	public String autoCreateAreaCode(AdministrativeArea area,AdministrativeArea parea) {
		
		int grade=Integer.parseInt(parea.getAreaLevel())+1;
		String sql="select max(area_code) from t_area where pid like'"+area.getPid()+"%' and area_level="+grade ;
	
		try{
			conn=MedicalDBUtil.getConnection();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			String maxcode="";
			Integer number=1;
			if(rs.next()){
			   maxcode=rs.getString(1);
			}
			
			if(maxcode!=null){
			  int beginIndex=maxcode.length()-2;
			  String no=maxcode.substring(beginIndex);
			  number=Integer.parseInt(no);
			  ++number;
			  //使用0补足位数
			  no=String.format("%02d", number);
			  maxcode=parea.getAreaCode()+no;
			}else{
				String no=String.format("%02d", number);
				maxcode=parea.getAreaCode()+no;
			}
			return maxcode;
		}catch(SQLException e){
			e.printStackTrace();
			throw new  RuntimeException("出现错误！");
		}finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
	}
	@Override
	public List<AdministrativeArea> findOtherAreaInfo() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_level=1 OR area_level=2;";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public List<AdministrativeArea> findContryAreaInfo() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE pid=0";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	
	@Override
	public List<AdministrativeArea> findTownAreaInfo(String areacode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_code LIKE '%"+areacode+"%' AND area_level=2;";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public List<AdministrativeArea> findVillageAreaInfo(String areacode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_code LIKE '%"+areacode+"%' AND area_level=3;";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public List<AdministrativeArea> findGroupAreaInfo(String areacode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_code LIKE '%"+areacode+"%' AND area_level=4;";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public List<String> findAreaNameBycode(String townCode, String contryCode, String villageCode, String groupCode) {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<String> findAreaName=new ArrayList<>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT area_name FROM t_area WHERE area_code=? OR area_code=? OR area_code=? OR area_code=?";
		ps=conn.prepareStatement(sql);
		ps.setString(1, contryCode);
		ps.setString(2, townCode);
		ps.setString(3, villageCode);
		ps.setString(4, groupCode);
		rs=ps.executeQuery();
		while(rs.next()){
	
		
		findAreaName.add(rs.getString(1));
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return findAreaName;
	}
	@Override
	public List<AdministrativeArea> findAllTownInfo() {
		
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_level=2;";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public List<AdministrativeArea> findContryAndTown() {
		try {
			conn = MedicalDBUtil.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		List<AdministrativeArea> areaList=new ArrayList<AdministrativeArea>();//创建list集合，用于保持User对象
		try {
		/*conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
*/		String sql="SELECT * FROM t_area WHERE area_level=1 OR area_level=2 ORDER BY area_level ";
		ps=conn.prepareStatement(sql);
		rs=ps.executeQuery();
		while(rs.next()){
		AdministrativeArea area = new AdministrativeArea();
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		
		areaList.add(area);
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return areaList;
	}
	@Override
	public AdministrativeArea findAreaInfoById(String id) {
		AdministrativeArea area = new AdministrativeArea();
	
		try {
		conn = MedicalDBUtil.getConnection();
		String sql="SELECT * FROM t_area WHERE id=? ";
		ps=conn.prepareStatement(sql);
		ps.setString(1, id);
		rs=ps.executeQuery();
		while(rs.next()){
		
		area.setId(rs.getInt(1));
		area.setPid(rs.getInt(2));
		area.setAreaCode(rs.getString(3));
		area.setAreaName(rs.getString(4));
		area.setAreaLevel(rs.getString(5));
		}
		} catch (SQLException e) {

		e.printStackTrace();
		}
		finally{
			MedicalDBUtil.closeAll(conn, ps, rs);
		}
		return area;
	}
	@Override
	public void editArea(AdministrativeArea area) {
		try {
			conn=MedicalDBUtil.getConnection();//通过工具类直接调用getConnection方法获取jdbc连接。
			//UPDATE student SET name='"+name+"', age=" + age+" where name='大红'";
			String sql="update t_area set area_name=? where id=?";
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, area.getAreaName());
			ps.setInt(2, area.getId());
			ps.executeUpdate();
			
			} catch (SQLException e) {

			e.printStackTrace();
			}finally{
				MedicalDBUtil.closeAll(conn, ps, rs);
			}
		
	}
	
	}


